﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {        

        #region 个人简历
        /// <summary>
        /// 发布简历
        /// </summary>
        /// <returns></returns>
        public int InsertResume(ResumeInfo resumeInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,resumeInfo.UserId),
                                      DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeInfo.ResumeId),
                                      DbHelper.MakeInParam("@area",(DbType)SqlDbType.NVarChar,50,resumeInfo.Area),
                                      DbHelper.MakeInParam("@education",(DbType)SqlDbType.NChar,10,resumeInfo.Education),
                                      DbHelper.MakeInParam("@email",(DbType)SqlDbType.NVarChar,50,resumeInfo.Email),
                                      DbHelper.MakeInParam("@index",(DbType)SqlDbType.NVarChar,50,resumeInfo.Index),
                                      DbHelper.MakeInParam("@marriage",(DbType)SqlDbType.NChar,10,resumeInfo.Marriage),
                                      DbHelper.MakeInParam("@mobile",(DbType)SqlDbType.NChar,16,resumeInfo.Mobile),
                                      DbHelper.MakeInParam("@myheight",(DbType)SqlDbType.NChar,10,resumeInfo.MyHeight),                                     
                                      DbHelper.MakeInParam("@name",(DbType)SqlDbType.NChar,20,resumeInfo.Name),
                                      DbHelper.MakeInParam("@notes",(DbType)SqlDbType.NVarChar,100,resumeInfo.Notes),
                                      DbHelper.MakeInParam("@papers",(DbType)SqlDbType.NChar,10,resumeInfo.Papers),
                                      DbHelper.MakeInParam("@papersnum",(DbType)SqlDbType.NChar,20,resumeInfo.PapersNum),
                                      DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NChar,16,resumeInfo.Phone),
                                      DbHelper.MakeInParam("@zipcode",(DbType)SqlDbType.NChar,10,resumeInfo.ZipCode),
                                      DbHelper.MakeInParam("@political",(DbType)SqlDbType.NChar,10,resumeInfo.Political),
                                      DbHelper.MakeInParam("@sex",(DbType)SqlDbType.NChar,12,resumeInfo.Sex),
                                      DbHelper.MakeInParam("@skills",(DbType)SqlDbType.NVarChar,1000,resumeInfo.Skills),
                                      DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,100,resumeInfo.Address),
                                      DbHelper.MakeInParam("@homeaddress",(DbType)SqlDbType.NVarChar,100,resumeInfo.HomeAddress),
                                      DbHelper.MakeInParam("@evaluation",(DbType)SqlDbType.NVarChar,1000,resumeInfo.Evaluation),
                                      DbHelper.MakeInParam("@workexp",(DbType)SqlDbType.NChar,12,resumeInfo.WorkExp), 
                                      DbHelper.MakeInParam("@birthdate",(DbType)SqlDbType.DateTime,resumeInfo.BirthDate.Length,resumeInfo.BirthDate),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,resumeInfo.Date.Length,resumeInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}resume] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([userid],[resumeid] ,[area],[education],[email],[index],[marriage],[mobile]");
            commantText.Append(",[myheight],[name],[notes],[papers],[papersnum],[phone],[zipcode],[political],[sex]");
            commantText.Append(" ,[skills],[address],[homeaddress],[evaluation],[workexp],[birthdate],[date])");
            commantText.Append("VALUES(@userid,@resumeid,@area,@education,@email,@index,@marriage,@mobile");
            commantText.Append(",@myheight,@name,@notes,@papers,@papersnum,@phone,@zipcode,@political,@sex");
            commantText.Append(",@skills,@address,@homeaddress,@evaluation,@workexp,@birthdate,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和resumeid更新简历信息
        /// </summary>
        /// <param name="resumeInfo"></param>
        /// <returns></returns>
        public int UpdateResumeByResumeIdAndUserID(ResumeInfo resumeInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,resumeInfo.UserId),
                                      DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeInfo.ResumeId),
                                      DbHelper.MakeInParam("@area",(DbType)SqlDbType.NVarChar,50,resumeInfo.Area),
                                      DbHelper.MakeInParam("@education",(DbType)SqlDbType.NChar,10,resumeInfo.Education),
                                      DbHelper.MakeInParam("@email",(DbType)SqlDbType.NVarChar,50,resumeInfo.Email),
                                      DbHelper.MakeInParam("@index",(DbType)SqlDbType.NVarChar,50,resumeInfo.Index),
                                      DbHelper.MakeInParam("@marriage",(DbType)SqlDbType.NChar,10,resumeInfo.Marriage),
                                      DbHelper.MakeInParam("@mobile",(DbType)SqlDbType.NChar,16,resumeInfo.Mobile),
                                      DbHelper.MakeInParam("@myheight",(DbType)SqlDbType.NChar,10,resumeInfo.MyHeight),                                     
                                      DbHelper.MakeInParam("@name",(DbType)SqlDbType.NChar,20,resumeInfo.Name),
                                      DbHelper.MakeInParam("@notes",(DbType)SqlDbType.NVarChar,100,resumeInfo.Notes),
                                      DbHelper.MakeInParam("@papers",(DbType)SqlDbType.NChar,10,resumeInfo.Papers),
                                      DbHelper.MakeInParam("@papersnum",(DbType)SqlDbType.NChar,20,resumeInfo.PapersNum),
                                      DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NChar,16,resumeInfo.Phone),
                                      DbHelper.MakeInParam("@zipcode",(DbType)SqlDbType.NChar,10,resumeInfo.ZipCode),
                                      DbHelper.MakeInParam("@political",(DbType)SqlDbType.NChar,10,resumeInfo.Political),
                                      DbHelper.MakeInParam("@sex",(DbType)SqlDbType.NChar,12,resumeInfo.Sex),
                                      DbHelper.MakeInParam("@skills",(DbType)SqlDbType.NVarChar,1000,resumeInfo.Skills),
                                      DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,100,resumeInfo.Address),
                                      DbHelper.MakeInParam("@homeaddress",(DbType)SqlDbType.NVarChar,100,resumeInfo.HomeAddress),
                                      DbHelper.MakeInParam("@evaluation",(DbType)SqlDbType.NVarChar,1000,resumeInfo.Evaluation),
                                      DbHelper.MakeInParam("@workexp",(DbType)SqlDbType.NChar,12,resumeInfo.WorkExp), 
                                      DbHelper.MakeInParam("@birthdate",(DbType)SqlDbType.DateTime,resumeInfo.BirthDate.Length,resumeInfo.BirthDate),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,resumeInfo.Date.Length,resumeInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}resume] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[area]=@area,[education]=@education,[email]=@email,[index]=@index,[marriage]=@marriage,[mobile]=@mobile");
            commantText.Append(",[myheight]=@myheight,[name]=@name,[notes]=@notes,[papers]=@papers,[papersnum]=@papersnum,[phone]=@phone,[zipcode]=@zipcode,[political]=@political,[sex]=@sex");
            commantText.Append(",[skills]=@skills,[address]=@address,[homeaddress]=@homeaddress,[evaluation]=@evaluation,[workexp]=@workexp,[birthdate]=@birthdate,[date]=@date");
            commantText.Append(" WHERE [resumeid]=@resumeid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        
        /// <summary>
        /// 根据resumeid更新简历信息
        /// </summary>
        /// <param name="resumeInfo"></param>
        /// <returns></returns>
        public int UpdateResumeByResumeId(ResumeInfo resumeInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeInfo.ResumeId),
                                      DbHelper.MakeInParam("@area",(DbType)SqlDbType.NVarChar,50,resumeInfo.Area),
                                      DbHelper.MakeInParam("@education",(DbType)SqlDbType.NChar,10,resumeInfo.Education),
                                      DbHelper.MakeInParam("@email",(DbType)SqlDbType.NVarChar,50,resumeInfo.Email),
                                      DbHelper.MakeInParam("@index",(DbType)SqlDbType.NVarChar,50,resumeInfo.Index),
                                      DbHelper.MakeInParam("@marriage",(DbType)SqlDbType.NChar,10,resumeInfo.Marriage),
                                      DbHelper.MakeInParam("@mobile",(DbType)SqlDbType.NChar,16,resumeInfo.Mobile),
                                      DbHelper.MakeInParam("@myheight",(DbType)SqlDbType.NChar,10,resumeInfo.MyHeight),                                     
                                      DbHelper.MakeInParam("@name",(DbType)SqlDbType.NChar,20,resumeInfo.Name),
                                      DbHelper.MakeInParam("@notes",(DbType)SqlDbType.NVarChar,100,resumeInfo.Notes),
                                      DbHelper.MakeInParam("@papers",(DbType)SqlDbType.NChar,10,resumeInfo.Papers),
                                      DbHelper.MakeInParam("@papersnum",(DbType)SqlDbType.NChar,20,resumeInfo.PapersNum),
                                      DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NChar,16,resumeInfo.Phone),
                                      DbHelper.MakeInParam("@zipcode",(DbType)SqlDbType.NChar,10,resumeInfo.ZipCode),
                                      DbHelper.MakeInParam("@political",(DbType)SqlDbType.NChar,10,resumeInfo.Political),
                                      DbHelper.MakeInParam("@sex",(DbType)SqlDbType.NChar,12,resumeInfo.Sex),
                                      DbHelper.MakeInParam("@skills",(DbType)SqlDbType.NVarChar,1000,resumeInfo.Skills),
                                      DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,100,resumeInfo.Address),
                                      DbHelper.MakeInParam("@homeaddress",(DbType)SqlDbType.NVarChar,100,resumeInfo.HomeAddress),
                                      DbHelper.MakeInParam("@evaluation",(DbType)SqlDbType.NVarChar,1000,resumeInfo.Evaluation),
                                      DbHelper.MakeInParam("@workexp",(DbType)SqlDbType.NChar,12,resumeInfo.WorkExp), 
                                      DbHelper.MakeInParam("@birthdate",(DbType)SqlDbType.DateTime,resumeInfo.BirthDate.Length,resumeInfo.BirthDate),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,resumeInfo.Date.Length,resumeInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}resume] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[area]=@area,[education]=@education,[email]=@email,[index]=@index,[marriage]=@marriage,[mobile]=@mobile");
            commantText.Append(",[myheight]=@myheight,[name]=@name,[notes]=@notes,[papers]=@papers,[papersnum]=@papersnum,[phone]=@phone,[zipcode]=@zipcode,[political]=@political,[sex]=@sex");
            commantText.Append(",[skills]=@skills,[address]=@address,[homeaddress]=@homeaddress,[evaluation]=@evaluation,[workexp]=@workexp,[birthdate]=@birthdate,[date]=@date");
            commantText.Append(" WHERE [resumeid]=@resumeid ");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 根据USERID和resumeid查找简历信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="resumeid"></param>
        /// <returns></returns>
        public DataTable GetResumeByResumeIdAndUserId(string userid,string resumeid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}resume] WHERE [userid]=@userid AND [resumeid]=@resumeid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 根据resumeid查找简历信息
        /// </summary>
        /// <param name="resumeid"></param>
        /// <returns></returns>
        public DataTable GetResumeByResumeId(string resumeid)
        {
            DbParameter parms = DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}resume] WHERE [resumeid]=@resumeid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有简历数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetResumeByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}resume] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 根据USERID和resumeid删除简历信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="resumeid"></param>
        /// <returns></returns>
        public int DelResumeByUserIdAndResumeId(string userid, string resumeid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}resume] WHERE [userid]=@userid AND [resumeid]=@resumeid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据resumeid删除简历信息
        /// </summary>
        /// <param name="resumeid"></param>
        /// <returns></returns>
        public int DelResumeByResumeId(string resumeid)
        {
            DbParameter parms = DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeid);
            string commandText = string.Format("DELETE FROM [dbo].[{0}resume] WHERE [resumeid]=@resumeid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得简历产品总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountResumeByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(resumeid) FROM [dbo].[{0}resume] WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        /// <summary>
        /// 根据userid查找resumeid
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public object GetResumeIdByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT [resumeid] FROM [dbo].[{0}resume] WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteScalar(CommandType.Text, commandText, parms);
        }

        #endregion

        #region 投放简历
        /// <summary>
        /// 发送简历
        /// </summary>
        /// <param name="resumeBoxInfo"></param>
        /// <returns></returns>
        public int InsertResumeBox(ResumeBoxInfo resumeBoxInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,resumeBoxInfo.UserId),
                                      DbHelper.MakeInParam("@resumeid",(DbType)SqlDbType.NVarChar,60,resumeBoxInfo.ResumeId),
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,resumeBoxInfo.JobsId),
                                      DbHelper.MakeInParam("@id",(DbType)SqlDbType.NVarChar,60,resumeBoxInfo.Id),                                      
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,resumeBoxInfo.Date.Length,resumeBoxInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}resumebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([id],[userid],[jobsid],[resumeid] ,[date])");
            commantText.Append("VALUES(@id,@userid,@jobsid,@resumeid,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 根据USERID和resumeid删除应聘简历
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelResumeBoxByUserIdAndResumeId(string userid, string id)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@id",(DbType)SqlDbType.NVarChar,60,id)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}resumebox] WHERE [userid]=@userid AND [id]=@id", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据resumeid删除应聘简历
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelResumeBoxByResumeId(string id)
        {
            DbParameter parms = DbHelper.MakeInParam("@id", (DbType)SqlDbType.NVarChar, 60, id);
            string commandText = string.Format("DELETE FROM [dbo].[{0}resumebox] WHERE [id]=@id", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 获取应聘简历
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetResumeBoxByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}resumebox] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得应聘简历总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountResumeBoxByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(id) FROM [dbo].[{0}resumebox] WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        /// <summary>
        /// 取得jobsid
        /// </summary>
        /// <param name="resumeid"></param>
        /// <returns></returns>
        public object GetResumeBoxInJobsIdByResumeId(string resumeid)
        {
            DbParameter parms = DbHelper.MakeInParam("@resumeid", (DbType)SqlDbType.NVarChar, 60, resumeid);
            string commandText = string.Format("SELECT [jobsid] FROM [dbo].[{0}resumebox] WHERE [resumeid]=@resumeid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteScalar(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 获取应聘记录信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetResumeBoxOrResumeOrJobsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT distinct a.[id],a.[userid],a.[resumeid],a.[date] ,b.[name],b.[sex],b.[education],c.[jobsid],c.[title],c.[paytreatment] FROM [dbo].[{0}resumebox] AS A INNER JOIN [dbo].[{0}resume] AS b ON a.[userid]=b.[userid]INNER JOIN [dbo].[{0}jobs] AS c ON a.[jobsid]=c.[jobsid] WHERE a.[userid]='123456' ORDER BY a.[date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }
        #endregion

    }
}